﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.Caching;
using IUAP.DAL.Provider;
using IUAP.DAL.Articulo;
/// <summary>
/// Descripción breve de SqlArticlesProvider
/// </summary>
namespace IUAP.DAL.SqlClient
{
    public class SqlArticuloProvider : ArticuloProvider
    {
        //----------------------Metodos para Categoria----------------------
        /// <summary>
        /// Devuelve una coleccion con todas las categorias
        /// </summary>
        public override List<CategoriaDetalles> GetCategoria()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getCategoria", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetCategoryCollectionFromReader(ExecuteReader(cmd));
            }
        }
        /// <summary>
        /// Devuelve la categoria con el id especificado
        /// </summary>
        public override CategoriaDetalles GetCategoriabyID(int id_categoria)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getCategoriabyID", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = id_categoria;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetCategoryFromReader(reader);
                else
                    return null;
            }
        }
        /// <summary>
        /// Borra una categoria
        /// </summary>
        public override bool DeleteCategoria(int id_categoria)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_deleteCategoria", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = id_categoria;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
        /// <summary>
        /// Actualiza una categoria
        /// </summary>
        public override bool UpdateCategoria(CategoriaDetalles category)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_updateCategoria", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = category.Id_categoria;
                cmd.Parameters.Add("@titu_cate", SqlDbType.NVarChar).Value = category.Titu_cate;
                cmd.Parameters.Add("@impo_cate", SqlDbType.Int).Value = category.Impo_cate;
                cmd.Parameters.Add("@imgurl_cate", SqlDbType.NVarChar).Value = category.Imgurl_cate;
                cmd.Parameters.Add("@desc_cate", SqlDbType.NVarChar).Value = category.Desc_cate;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);                
                return (ret == 1);
            }
        }
        /// <summary>
        /// Crea e inserta una nueva categoria
        /// </summary>
        public override int InsertCategoria(CategoriaDetalles category)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_insertCategoria", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = category.Id_categoria;
                cmd.Parameters.Add("@fech_cate", SqlDbType.DateTime).Value = category.Fech_cate;
                cmd.Parameters.Add("@usua_cate", SqlDbType.NVarChar).Value = category.Usua_cate;
                cmd.Parameters.Add("@titu_cate", SqlDbType.NVarChar).Value = category.Titu_cate;
                cmd.Parameters.Add("@impo_cate", SqlDbType.Int).Value = category.Impo_cate;
                cmd.Parameters.Add("@imgurl_cate", SqlDbType.NVarChar).Value = category.Imgurl_cate;
                cmd.Parameters.Add("@desc_cate", SqlDbType.NVarChar).Value = category.Desc_cate;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@id_categoria"].Value;
            }
        }

        //----------------------Metodos para Articulos----------------------
        /// <summary>
        /// Devuelve todos los articulos entre las paginas especificadas
        /// </summary>
        public override List<ArticuloDetalles> GetArticulo(int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getArticulo", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }
        /// <summary>
        /// Devuelve todos los articulos en una categoria especificada
        /// </summary>
        public override List<ArticuloDetalles> GetArticulobyCategoria(int id_categoria, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getArticulobyCategoria", cn);
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = id_categoria;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }
        /// <summary>
        /// Devuelve la cantidad total de articulos.
        /// </summary>
        public override int GetArticleCount()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getArticuloCount", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }
        /// <summary>
        /// Devuelve la cantidad total de articulos en una categoria especifica
        /// </summary>
        /// <param name="id_categoria"></param>
        /// <returns></returns>
        
        public override int GetContarArticulosbyCategoria(int id_categoria)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getContarArticulosbyCategoria", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = id_categoria;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }
              
        /// <summary>
        /// Devuelve todos los articulos publicados a la fecha
        /// </summary>
        public override List<ArticuloDetalles> GetArticuloPublicado(DateTime fecha_actual, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getArticuloPublicado", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@fecha_actual", SqlDbType.DateTime).Value = fecha_actual;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }
        /// <summary>
        /// Devuelve todos los articulos publicados en una determinada categoria
        /// </summary>
        public override List<ArticuloDetalles> GetArticuloPublicadobyCategoria(int id_categoria, DateTime fecha_actual, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getArticuloPublicadobyCategoria", cn);
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = id_categoria;
                cmd.Parameters.Add("@fecha_actual", SqlDbType.DateTime).Value = fecha_actual;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }
        /// <summary>
        /// Devuelve la cantidad de articulos publicados a la fecha
        /// </summary>
        public override int GetContarArticulosPublicados(DateTime fecha_actual)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getContarArticulosPublicados", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@fecha_actual", SqlDbType.DateTime).Value = fecha_actual;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }
        
        /// <summary>
        /// Devuelve la cantidad de articulos publicados en una determinada categoria
        /// </summary>
        public override int GetContarArticulosPublicadosbyCategoria(int id_categoria, DateTime fecha_actual)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getContarArticulosPublicadosbyCategoria", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = id_categoria;
                cmd.Parameters.Add("@fecha_actual", SqlDbType.DateTime).Value = fecha_actual;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }
        /// <summary>
        /// Devuelve el articulo con el id especificado.
        /// </summary>
        public override ArticuloDetalles GetArticulobyID(int id_articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getArticulobyID", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetArticleFromReader(reader, true);
                else
                    return null;
            }
        }
        /// <summary>
        /// Devuelve el contenido del articulo especificado.
        /// </summary>
        public override string GetArticuloCont(int id_articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getArticuloCont", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cn.Open();
                return (string)ExecuteScalar(cmd);
            }
        }
        /// <summary>
        /// Crea e inserta un nuevo articulo
        /// </summary>
        public override int InsertArticulo(ArticuloDetalles articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_insertArticulo", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@fech_arti", SqlDbType.DateTime).Value = articulo.Fech_arti;
                cmd.Parameters.Add("@usua_arti", SqlDbType.NVarChar).Value = articulo.Usua_arti;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = articulo.Id_categoria;
                cmd.Parameters.Add("@titu_arti", SqlDbType.NVarChar).Value = articulo.Titu_arti;
                cmd.Parameters.Add("@resu_arti", SqlDbType.NVarChar).Value = articulo.Resu_arti;
                cmd.Parameters.Add("@cont_arti", SqlDbType.NText).Value = articulo.Cont_arti;
                cmd.Parameters.Add("@ciud_arti", SqlDbType.NVarChar).Value = articulo.Ciud_arti;
                cmd.Parameters.Add("@fech_publ_arti", SqlDbType.DateTime).Value = articulo.Fech_publ_arti;
                cmd.Parameters.Add("@fech_expi_arti", SqlDbType.DateTime).Value = articulo.Fech_expi_arti;
                cmd.Parameters.Add("@apro_arti", SqlDbType.Bit).Value = articulo.Apro_arti;
                cmd.Parameters.Add("@list_arti", SqlDbType.Bit).Value = articulo.List_arti;
                cmd.Parameters.Add("@actv_comn_arti", SqlDbType.Bit).Value = articulo.Actv_comn_arti;
                cmd.Parameters.Add("@solo_miem_arti", SqlDbType.Bit).Value = articulo.Solo_miem_arti;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@id_articulo"].Value;
            }
        }
        /// <summary>
        /// Actualiza un articulo
        /// </summary>
        public override bool UpdateArticulo(ArticuloDetalles articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_updateArticulo", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = articulo.Id_articulo;
                cmd.Parameters.Add("@id_categoria", SqlDbType.Int).Value = articulo.Id_categoria;
                cmd.Parameters.Add("@titu_arti", SqlDbType.NVarChar).Value = articulo.Titu_arti;
                cmd.Parameters.Add("@resu_arti", SqlDbType.NVarChar).Value = articulo.Resu_arti;
                cmd.Parameters.Add("@cont_arti", SqlDbType.NText).Value = articulo.Cont_arti;
                cmd.Parameters.Add("@ciud_arti", SqlDbType.NVarChar).Value = articulo.Ciud_arti;
                cmd.Parameters.Add("@fech_publ_arti", SqlDbType.DateTime).Value = articulo.Fech_publ_arti;
                cmd.Parameters.Add("@fech_expi_arti", SqlDbType.DateTime).Value = articulo.Fech_expi_arti;
                cmd.Parameters.Add("@apro_arti", SqlDbType.Bit).Value = articulo.Apro_arti;
                cmd.Parameters.Add("@list_arti", SqlDbType.Bit).Value = articulo.List_arti;
                cmd.Parameters.Add("@actv_comn_arti", SqlDbType.Bit).Value = articulo.Actv_comn_arti;
                cmd.Parameters.Add("@solo_miem_arti", SqlDbType.Bit).Value = articulo.Solo_miem_arti;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
        /// <summary>
        /// Borra un articulo
        /// </summary>
        public override bool DeleteArticulo(int id_articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_deleteArticulo", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
        /// <summary>
        /// Aprueba un articulo determinado.
        /// </summary>
        public override bool AprobarArticulo(int id_articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_aprobarArticulo", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
        /// <summary>
        /// Aumenta la cantidad de visitas de un articulo determinado.
        /// </summary>
        public override bool IncrementCantidadVisitas(int id_articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_incrementCantidadVisitas", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
        /// <summary>
        /// Inserta un voto a un determinado articulo
        /// </summary>
        public override bool InsertVoto(int id_articulo, int rating)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_insertVoto", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cmd.Parameters.Add("@rating", SqlDbType.Int).Value = rating;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
        //----------------------Metodos para Comentario----------------------
        /// <summary>
        /// Devuelve todos los comentarios
        /// </summary>
        public override List<ComentarioDetalles> GetComentario(int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getComentario", cn);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetCommentCollectionFromReader(ExecuteReader(cmd));
            }
        }
        /// <summary>
        /// Devuelve la cantidad total de comentarios
        /// </summary>
        public override int GetContarComentario()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getContarComentario", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }
        /// <summary>
        /// Devuelve todos los comentarios de un articulo determinado
        /// </summary>
        public override List<ComentarioDetalles> GetComentariobyArticulo(int id_articulo, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getComentariobyArticulo", cn);
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetCommentCollectionFromReader(ExecuteReader(cmd));
            }
        }
        /// <summary>
        /// Devuelve la cantidad de comentarios de un articulo determinado
        /// </summary>
        public override int GetContarComentariobyArticulo(int id_articulo)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getContarComentariobyArticulo", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = id_articulo;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }
        /// <summary>
        /// Devuelve un detemrinado comentario
        /// </summary>
        public override ComentarioDetalles GetComentariobyID(int id_comentario)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_getComentariobyID", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_comentario", SqlDbType.Int).Value = id_comentario;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetCommentFromReader(reader);
                else
                    return null;
            }
        }
        /// <summary>
        /// Borra un comentario
        /// </summary>
        public override bool DeleteComentario(int id_comentario)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_deleteComentario", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_comentario", SqlDbType.Int).Value = id_comentario;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
        /// <summary>
        /// Inserta un nuevo comentario
        /// </summary>
        public override int InsertComentario(ComentarioDetalles comentario)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_insertComentario", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@fech_come", SqlDbType.DateTime).Value = comentario.Fech_come;
                cmd.Parameters.Add("@usua_come", SqlDbType.NVarChar).Value = comentario.Usua_come;
                cmd.Parameters.Add("@mail_usua_come", SqlDbType.NVarChar).Value = comentario.Mail_usua_come;
                cmd.Parameters.Add("@ip_usua_come", SqlDbType.NVarChar).Value = comentario.Ip_usua_come;
                cmd.Parameters.Add("@id_articulo", SqlDbType.Int).Value = comentario.Id_articulo;
                cmd.Parameters.Add("@cont_come", SqlDbType.NVarChar).Value = comentario.Cont_come;
                cmd.Parameters.Add("@id_comentario", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@id_comentario"].Value;
            }
        }
        /// <summary>
        /// Actualiza el contenido de un comentario
        /// </summary>
        public override bool UpdateComentario(ComentarioDetalles comentario)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("tbi_Articulo_updateComentario", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id_comentario", SqlDbType.Int).Value = comentario.Id_comentario;
                cmd.Parameters.Add("@cont_come", SqlDbType.NVarChar).Value = comentario.Cont_come;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }
    }
}